Imports System.Windows.Forms
Public Class ManageReportControl

    Public Function getSummaryReport(ByVal inYear As String, ByVal inOrganizationID As String, ByVal inReportType As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inReportType", inReportType)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryReport")

        Return objDs
    End Function

    Public Function getSummaryNoExamReport(ByVal inYear As String, ByVal inOrganizationID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_NoExamReport"
        objCmd.CommandTimeout = 0

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryNoExamReport")

        Return objDs
    End Function

    Public Function getSummaryAbnormalReport(ByVal inYear As String, ByVal inOrganizationID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryAbnormalReport"
        objCmd.CommandTimeout = 0

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryAbnormalReport")

        Return objDs
    End Function

    Public Function getSummaryPoliceReport(ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceReport")

        Return objDs
    End Function

    Public Function getSummaryPoliceBMI(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceBMIReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceBMI")

        Return objDs
    End Function

    Public Function getSummaryPoliceCBC(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceCBCReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceCBC")

        Return objDs
    End Function

    Public Function getSummaryPoliceCXR(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceCXRReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceCXR")

        Return objDs
    End Function

    Public Function getSummaryPoliceUrine(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceUrineReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceUrine")

        Return objDs
    End Function

    Public Function getSummaryPoliceBiochemistry(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceBiochemistryReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceBiochemistry")

        Return objDs
    End Function

    Public Function getSummaryPoliceEKG(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceEKGReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceEKG")

        Return objDs
    End Function

    Public Function getSummaryPoliceStoolExam(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceStoolExamReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceStoolExam")

        Return objDs
    End Function

    Public Function getSummaryPoliceStoolParasite(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceStoolParasiteReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceStoolParasite")

        Return objDs
    End Function

    Public Function getSummaryPoliceStoolOther(ByVal inYear As String, ByVal inCitizenID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceStoolOtherReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryPoliceStoolOther")

        Return objDs
    End Function

    Public Function getSummaryReportYear() As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryReportYear"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryReportYear")

        Return objDs
    End Function

    Public Function getSummaryAbnormalReportYear() As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryAbnormalReportYear"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryReportYear")

        Return objDs
    End Function

    Public Function getSummaryNoExamReportYear() As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryNoExamReportYear"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryReportYear")

        Return objDs
    End Function

    Public Function getSummaryReportOrganization(ByVal inOrganizationID As String, ByVal inRBNName As String) As DataSet
        Dim objDs As New System.Data.DataSet()
        Select Case inRBNName
            Case "rbnReglion"
                Select Case inOrganizationID
                    Case "10000"
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_OrganizationChild"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationParentID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                    Case "98000"
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_OrganizationChild"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationParentID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                    Case "99000"
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_OrganizationChild"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationParentID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                    Case Else
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_Organization"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                End Select

            Case "rbnProvince"
                Select Case inOrganizationID
                    Case "00000"
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_OrganizationIsProvince"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationParentID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                    Case "50040"
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_OrganizationIDParentandChild"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                    Case "65020"
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_OrganizationIDParentandChild"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                    Case Else
                        Dim decryptText As New DateUtilities.Encrypt_Data()
                        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                            "PoliceHealthConnectionString").ToString(), True))
                        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                        objCmd.Connection = objConn
                        objCmd.CommandType = CommandType.StoredProcedure
                        objCmd.CommandText = "proc_SELECT_Organization"

                        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
                        objCmd.Parameters.Add(objParam)

                        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                        objDa.Fill(objDs, "SummaryReportOrganization")
                End Select

            Case "rbnAmphur"
                Dim decryptText As New DateUtilities.Encrypt_Data()
                Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                    decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                    "PoliceHealthConnectionString").ToString(), True))
                Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
                objCmd.Connection = objConn
                objCmd.CommandType = CommandType.StoredProcedure
                objCmd.CommandText = "proc_SELECT_SummaryReportOrganization"

                Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
                objCmd.Parameters.Add(objParam)

                Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
                objDa.Fill(objDs, "SummaryReportOrganization")
        End Select
        Return objDs
    End Function

    Public Function getReportOrganizationByProvince(ByVal inProvinceID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.Text
        objCmd.CommandText = "SELECT * FROM trorganization WHERE province_id = '" & inProvinceID & "' AND organization_id NOT IN ('15000', '50000', '51000', '52000', '53000', '54000', '55000', '56000', '57000', '58000', '99000')"
        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PrintPoliceOrganization")

        Return objDs
    End Function

    Public Function getPrintPoliceReport(ByVal inOrganizationID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PrintPolice"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PrintPoliceReport")

        Return objDs
    End Function

    Public Function getSummaryReportOrganizationName(ByVal inOrganizationID As String) As String
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_OrganizationName"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryReportOrganizationName")

        Return objDs.Tables("SummaryReportOrganizationName").Rows(0).Item("organization_name_tha").ToString()
    End Function

    Public Function getSummaryReportOrganizationParentName(ByVal inOrganizationID As String) As String
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_OrganizationParent"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryReportOrganizationParentName")

        Return objDs.Tables("SummaryReportOrganizationParentName").Rows(0).Item("organization_name_tha").ToString()
    End Function

    Public Function getSummaryReportPoliceInOrganization(ByVal inOrganizationID As String, ByVal inYear As String) As Integer
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_COUNT_PoliceInOrganization"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "SummaryReportPoliceInOrganization")

        Return CInt(objDs.Tables("SummaryReportPoliceInOrganization").Rows(0).Item(0))
    End Function

    Public Function getProvince() As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_Province"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "Province")

        Return objDs
    End Function

    Public Function getOrganization(ByVal inProvinceID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_DiagnosePriceOrganization"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inProvinceID", inProvinceID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "Organization")

        Return objDs
    End Function

    Public Function getAudiometryPoliceReport(ByVal inCitizenID As String, ByVal inYear As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_SummaryPoliceAudiometryReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceAudiometryReport")

        Return objDs
    End Function

    Public Function getDiagnosePriceReport(ByVal inOrganizationID, ByVal inYear) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_DiagnosePriceReport"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "DiagnosePrice")

        Return objDs
    End Function

    Public Function getOtherPriceReport(ByVal inProvince As String, ByVal inYear As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_OtherPriceReportData"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inProvinceID", inProvince)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "OtherPrice")

        Return objDs
    End Function

    Public Function getOtherLabPrice(ByVal inYear As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_OtherLabPrice"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "OtherLabPrice")

        Return objDs
    End Function

    Public Function getFrequency() As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_Frequency"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "Frequency")

        Return objDs
    End Function
End Class
